package com.carrental.dao;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.carrental.entity.CarList;
import com.carrental.util.DBConnection;

//汽车数据操作
public class CarListDao {
	
	DBConnection dbConnection;
	
	//添加汽车
	public int add(CarList carList) throws Exception {
		dbConnection = new DBConnection();
		String sql = "insert into car_list(id,model,number,price,create_datetime,create_username) values(?,?,?,?,?,?)";
		int res = dbConnection.executeUpdate(sql, new String[] {
				carList.getId(), carList.getModel(), carList.getNumber()+"", carList.getPrice().toString(), carList.getCreateDatetime().toString(), carList.getCreateUsername()
		});
		dbConnection.closeAll();
		return res;
	}
	
	//删除汽车
	public int delete(String id) throws Exception {
		dbConnection = new DBConnection();
		String sql = "delete from car_list where id = ?";
		int res = dbConnection.executeUpdate(sql, new String[] {id});
		dbConnection.closeAll();
		return res;
	}
	
	//更新汽车
	public int update(CarList carList) throws Exception {
		dbConnection = new DBConnection();
		String sql = "update car_list set model = ?, number = ?, price = ? where id = ?";
		int res = dbConnection.executeUpdate(sql, new String[] {
				carList.getModel(), carList.getNumber()+"", carList.getPrice().toString(), carList.getId()
		});
		dbConnection.closeAll();
		return res;
	}
	
	//查询所有汽车
	public List<CarList> selectAll() throws Exception {
		dbConnection = new DBConnection();
		String sql = "select * from car_list";
		ResultSet res = dbConnection.executeQuery(sql);
		List<CarList> list = new ArrayList<CarList>();
		while (res.next()) {
			CarList carList = new CarList();
			carList.setId(res.getString("id"));
			carList.setModel(res.getString("model"));
			carList.setNumber(res.getInt("number"));
			carList.setPrice(res.getDouble("price"));
			carList.setCreateDatetime(res.getTimestamp("create_datetime"));
			carList.setCreateUsername(res.getString("create_username"));
			list.add(carList);
		}
		dbConnection.closeAll();
		return list;
	}
	
	//根据id查询汽车
	public CarList selectById(String id) throws Exception {
		dbConnection = new DBConnection();
		String sql = "select * from car_list where id = ?";
		ResultSet res = dbConnection.executeQuery(sql, new String[] {id});
		CarList carList = new CarList();
		while (res.next()) {
			carList.setId(res.getString("id"));
			carList.setModel(res.getString("model"));
			carList.setNumber(res.getInt("number"));
			carList.setPrice(res.getDouble("price"));
			carList.setCreateDatetime(res.getTimestamp("create_datetime"));
			carList.setCreateUsername(res.getString("create_username"));
		}
		dbConnection.closeAll();
		return carList;
	}
	
	//查询有库存的
	public List<CarList> selectHaveNumber() throws Exception {
		dbConnection = new DBConnection();
		String sql = "select * from car_list where number != 0";
		ResultSet res = dbConnection.executeQuery(sql);
		List<CarList> list = new ArrayList<CarList>();
		while (res.next()) {
			CarList carList = new CarList();
			carList.setId(res.getString("id"));
			carList.setModel(res.getString("model"));
			carList.setNumber(res.getInt("number"));
			carList.setPrice(res.getDouble("price"));
			carList.setCreateDatetime(res.getTimestamp("create_datetime"));
			carList.setCreateUsername(res.getString("create_username"));
			list.add(carList);
		}
		dbConnection.closeAll();
		return list;
	}

}
